﻿IF  NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[JPushMessageLog]') AND type in (N'U'))

CREATE TABLE [dbo].[JPushMessageLog](
    [Key] [uniqueidentifier] NOT NULL DEFAULT NEWID(),
    [MessageId] [int] NOT NULL DEFAULT 0,
    [ReceiverValue] [nvarchar](max) NULL,
    [Content] [nvarchar](max) NOT NULL,
    [Platform] [int] NOT NULL DEFAULT 0,
    [CustomizedValue] [xml] NULL,
    [MessageType] [int] NOT NULL DEFAULT 0,
    [PushType] [int] NOT NULL DEFAULT 0,
    [ResponseCode] [INT] NOT NULL DEFAULT 0,
    [ResponseMessage] [nvarchar](max) NULL,
    [PushTitle] [nvarchar](max) NULL,
    [BuilderId] [nvarchar](max) NULL,
    [AndroidDeliveredCount] [int] NULL,
    [ApplePushNotificationDeliveredCount] [int] NULL,
    [IsTest] [BIT] NOT NULL DEFAULT 0,
    [CreatedStamp] [datetime] NOT NULL DEFAULT GETUTCDATE(),
    [LastUpdatedStamp] [datetime] NOT NULL DEFAULT GETUTCDATE()
CONSTRAINT [PK_JPushMessageLog] PRIMARY KEY CLUSTERED 
(
    [Key] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO




IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_ReformatStatement]'))
DROP FUNCTION [dbo].[fn_ReformatStatement]
GO

CREATE FUNCTION [dbo].[fn_ReformatStatement](
	@Value NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
	IF(@Value IS NOT NULL)
	BEGIN
		SET @Value = RTRIM(LTRIM(@Value));
		SET @Value = REPLACE(@Value,'--','');
		SET @Value = REPLACE(@Value,'''','');
		SET @Value = REPLACE(@Value,'"','');
		SET @Value = REPLACE(@Value,',','');
		SET @Value = REPLACE(@Value,';','');
		SET @Value = REPLACE(@Value,'/','');
		SET @Value = REPLACE(@Value,'\','');
	END

	RETURN @Value;
END
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_GenerateSqlExpression]'))
DROP FUNCTION [dbo].[fn_GenerateSqlExpression]
GO
/*
Sample: 
	dbo.fn_GenerateSqlExpression('Name','Like','%W', 1)
	RETURNS: [Name] Like '%W'
Sample: 
	dbo.fn_GenerateSqlExpression('CreatedStamp','>','2010-01-02', 1)
	RETURNS: [CreatedStamp] > '2010-01-02'
Sample: 
	dbo.fn_GenerateSqlExpression('FileSize','<=','3600', 0)
	RETURNS: [FileSize] <= 3600
*/
CREATE FUNCTION [dbo].[fn_GenerateSqlExpression](
	@ColumnName NVARCHAR(MAX),
	@Operator NVARCHAR(MAX),
	@Value NVARCHAR(MAX),
	@IsStringType BIT
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
	DECLARE @Result AS NVARCHAR(MAX);
	SET @Result = '';

	IF @ColumnName IS NOT NULL
		SET @ColumnName = dbo.fn_ReformatStatement(@ColumnName);

	IF @Operator IS NOT NULL
		SET @Operator = dbo.fn_ReformatStatement(@Operator);

	IF @Value IS NOT NULL
		SET @Value = dbo.fn_ReformatStatement(@Value);
		
	IF @ColumnName <> '' AND @Operator <> ''
	BEGIN
		IF(@Value IS NULL)
			SET @Result = '['+ @ColumnName + '] IS NULL';
		ELSE
			SET @Result = '['+ @ColumnName + '] ' + @Operator + ' '
				+ (CASE WHEN @IsStringType = 1 THEN '''' ELSE '' END)
				+ @Value
				+ (CASE WHEN @IsStringType = 1 THEN '''' ELSE '' END);
			
	END

	RETURN @Result;
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'fn_GenerateWherePattern'))
DROP FUNCTION [fn_GenerateWherePattern]
GO

CREATE FUNCTION [fn_GenerateWherePattern](
	@ColumnName NVARCHAR(MAX),
	@Operator NVARCHAR(MAX),
	@Value NVARCHAR(MAX),
	@IsStringType BIT
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
	DECLARE @Result AS NVARCHAR(MAX);
	SET @Result = '';

	IF @Value IS NOT NULL AND @ColumnName IS NOT NULL AND @Operator IS NOT NULL
		BEGIN
			SET @Value = dbo.fn_ReformatStatement(@Value);

			IF @Value <> ''
				BEGIN
					IF LOWER(@Operator) = 'like'
						BEGIN
							SET @Value = '%'+@Value+'%';
							SET @IsStringType = 1;
						END
					SET @Result =  dbo.fn_GenerateSqlExpression(@ColumnName,@Operator,@Value, @IsStringType) + ' AND ';
				END
		END

	RETURN @Result;
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_CreateOrUpdateJPushMessageLog]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_CreateOrUpdateJPushMessageLog]
GO

CREATE PROCEDURE [dbo].[sp_CreateOrUpdateJPushMessageLog](
    @MessageId INT,
    @ReceiverValue NVARCHAR(MAX),
    @Content NVARCHAR(MAX),
    @Platform INT,
    @CustomizedValue XML,
    @MessageType INT,
    @PushType INT,
    @ResponseCode INT,
    @ResponseMessage NVARCHAR(MAX),
    @PushTitle NVARCHAR(MAX),
    @BuilderId NVARCHAR(MAX),
    @AndroidDeliveredCount INT,
    @ApplePushNotificationDeliveredCount INT,
    @IsTest BIT
)
AS

BEGIN
    DECLARE @Key AS UNIQUEIDENTIFIER;
    DECLARE @NowTime AS DATETIME = GETUTCDATE();

    IF @MessageId IS NOT NULL
    BEGIN

        SELECT @Key = [Key]
            FROM [dbo].[JPushMessageLog]
            WHERE [MessageId] = @MessageId;

        IF @Key IS NULL
        BEGIN
            SET @Key = NEWID();

             INSERT INTO [dbo].[JPushMessageLog]
               ([Key]
               ,[MessageId]
               ,[ReceiverValue]
               ,[Content]
               ,[Platform]
               ,[CustomizedValue]
               ,[MessageType]
               ,[PushType]
               ,[ResponseCode]
               ,[ResponseMessage]
               ,[PushTitle]
               ,[BuilderId]
               ,[AndroidDeliveredCount]
               ,[ApplePushNotificationDeliveredCount]
               ,[IsTest]
               ,[CreatedStamp]
               ,[LastUpdatedStamp])
               VALUES(
                @Key,
                @MessageId,
                @ReceiverValue,
                @Content,
                @Platform,
                @CustomizedValue,
                @MessageType,
                @PushType,
                @ResponseCode,
                @ResponseMessage,
                @PushTitle,
                @BuilderId,
                @AndroidDeliveredCount,
                @ApplePushNotificationDeliveredCount,
                @IsTest,
                @NowTime,
                @NowTime
               );
        END
        ELSE
        BEGIN
            UPDATE [dbo].[JPushMessageLog]
                SET [ApplePushNotificationDeliveredCount] = @ApplePushNotificationDeliveredCount,
                    [AndroidDeliveredCount] = @AndroidDeliveredCount,
                    [LastUpdatedStamp] = @NowTime
                WHERE [Key] = @Key;
        END
    END 
END
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_GetActiveJPushMessage]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_GetActiveJPushMessage]
GO

CREATE PROCEDURE [dbo].[sp_GetActiveJPushMessage]
AS

BEGIN
    SELECT [Key]
      ,[MessageId]
      ,[ReceiverValue]
      ,[Content]
      ,[Platform]
      ,[CustomizedValue]
      ,[MessageType]
      ,[PushType]
      ,[ResponseCode]
      ,[ResponseMessage]
      ,[PushTitle]
      ,[BuilderId]
      ,[AndroidDeliveredCount]
      ,[ApplePushNotificationDeliveredCount]
      ,[IsTest]
      ,[CreatedStamp]
      ,[LastUpdatedStamp]
  FROM [dbo].[JPushMessageLog]
  WHERE DATEADD(hh, 240, [CreatedStamp]) >= GETUTCDATE();

END
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_QueryJPushMessageLog]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_QueryJPushMessageLog]
GO

CREATE PROCEDURE [dbo].[sp_QueryJPushMessageLog](
    @MessageId INT,
    @ReceiverValue NVARCHAR(MAX),
    @Content NVARCHAR(MAX),
    @Platform INT,
    @MessageType INT,
    @PushType INT,
    @IsTest BIT,
    @OrderDesc BIT
)
AS

BEGIN
    DECLARE @SqlStatement AS NVARCHAR(MAX);
    DECLARE @WhereStatement AS NVARCHAR(MAX) = '';

    SET @SqlStatement = 'SELECT [Key]
      ,[MessageId]
      ,[ReceiverValue]
      ,[Content]
      ,[Platform]
      ,[CustomizedValue]
      ,[MessageType]
      ,[PushType]
      ,[ResponseCode]
      ,[ResponseMessage]
      ,[PushTitle]
      ,[BuilderId]
      ,[AndroidDeliveredCount]
      ,[ApplePushNotificationDeliveredCount]
      ,[IsTest]
      ,[CreatedStamp]
      ,[LastUpdatedStamp]
  FROM [dbo].[JPushMessageLog]';

    SET @MessageId = [dbo].[fn_ReformatStatement](@MessageId);

    IF @MessageId IS NOT NULL
    BEGIN
        SET @WhereStatement = '[MessageId] = ' + CONVERT(NVARCHAR(MAX), @MessageId) + ' AND ';
    END
    ELSE
    BEGIN
        SET @WhereStatement = @WhereStatement + dbo.[fn_GenerateWherePattern]('ReceiverValue','LIKE',@ReceiverValue,1);
        SET @WhereStatement = @WhereStatement + dbo.[fn_GenerateWherePattern]('Content','LIKE',@Content,1);
        SET @WhereStatement = @WhereStatement + dbo.[fn_GenerateWherePattern]('MessageType','=',CONVERT(NVARCHAR(MAX), @MessageType),0);
        SET @WhereStatement = @WhereStatement + dbo.[fn_GenerateWherePattern]('PushType','=',CONVERT(NVARCHAR(MAX), @PushType),0);

        IF @Platform IS NOT NULL
            SET @WhereStatement = @WhereStatement + '([Platform] & ' + CONVERT(NVARCHAR(MAX), @Platform) + ')> 0 AND ';

        SET @WhereStatement = @WhereStatement + dbo.[fn_GenerateWherePattern]('IsTest','=',CONVERT(NVARCHAR(MAX),@IsTest),0);
    END

    IF(@WhereStatement <> '')
    BEGIN
        SET @WhereStatement = SUBSTRING(@WhereStatement, 0, LEN(@WhereStatement) - 3);
        SET @SqlStatement = @SqlStatement + ' WHERE ' + @WhereStatement;
        SET @SqlStatement = @SqlStatement + ' ORDER BY [MessageId] ';

        IF @OrderDesc IS NOT NULL AND @OrderDesc = 1
            SET @SqlStatement = @SqlStatement + 'DESC';
    END

    EXECUTE sp_executesql @SqlStatement;

END
GO